package com.sds.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import com.sds.fw.dao.AbDao;
import com.sds.shop.vo.BoardVO;
import com.sds.shop.vo.MemVO;

public class BoardDAO extends AbDao{
	private static BoardDAO dao = new BoardDAO();
	
	public static BoardDAO getInstance(){
		return dao;
	}
	
	public void insertBoard(BoardVO bvo)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try 
		{
			conn = connect();
			StringBuffer query = new StringBuffer();
			query.append("Insert into TB_BOARD " +
					"values(?, ?, ?, ?, ?, ?, ?, ?)");
			ps = conn.prepareStatement( query.toString() ); 
			ps.setString(1, bvo.getBoard_no());
			ps.setString(2, bvo.getMem_id());
			ps.setString(3, bvo.getTitle());		
			ps.setString(4, bvo.getText());
			ps.setInt(5, bvo.getCount());
			ps.setString(6, bvo.getDel_flg());
			ps.setString(7, bvo.getReg_dtm());
			ps.setString(8, bvo.getMod_dtm());
			ps.executeUpdate();
			conn.commit();
			conn.close();
			
		} catch(Exception ex){
			System.out.println("write exception : " + ex);
			close(conn, ps, rs);			
		}
	}
	
	public String GetnewNumber()
	{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String newboardno="";
		try{
			conn = connect();
			stmt=conn.createStatement();
			
			StringBuffer query = new StringBuffer();
			query.append("SELECT board_no from TB_BOARD order by board_no desc");		
			rs = stmt.executeQuery(query.toString());
			rs.next();
			int number=Integer.parseInt(rs.getString(1))+1;
			newboardno+=number;
			return newboardno;
			
	
			}
		catch(Exception ex){
			System.out.println("GetnewConnt Exception : " + ex);
		}
		
		return newboardno;	
	}
	
	public ArrayList <BoardVO> getBoardlist() {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList <BoardVO> arr_bv = new ArrayList<BoardVO>();
		try {
			conn=connect();
			String query="SELECT board_no, title, mem_id, reg_dtm, count from tb_board order by board_no desc";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(query);
			while(rs.next())
			{
				BoardVO bv = new BoardVO();
				bv.setBoard_no(rs.getString(1));
				bv.setTitle(rs.getString(2));				
				bv.setMem_id(rs.getString(3));				
				bv.setReg_dtm(rs.getString(4));				
				bv.setCount(rs.getInt(5));
				arr_bv.add(bv);
			}
			return arr_bv;
		} catch (Exception ex) {
			System.out.println("getCustomer exception : " + ex);
		}
		return arr_bv;
	}
	
	public BoardVO getBoardDetail(String boardno)
	{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		BoardVO bvo = new BoardVO();
		try{
			conn=connect();
			String query="SELECT title, mem_id, reg_dtm,text from tb_board where board_no='"+boardno+"'";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(query);
			rs.next();
			bvo.setTitle(rs.getString(1));
			bvo.setMem_id(rs.getString(2));
			bvo.setReg_dtm(rs.getString(3));
			bvo.setText(rs.getString(4));
			return bvo;
			
			
		}
		catch(Exception ex)
		{
			System.out.println("getBoardDetail exception : " + ex);
		}
		return bvo;		
	}	
}
